package com.bieyang.anjia.sqlitedatabase;

import java.util.ArrayList;
import com.bieyang.anjia.module.FileModel;
import com.bieyang.anjia.module.NewsInfo;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class SqliteOperation {
	AnjiaSQLiteOpenHelper helper;

	public SqliteOperation(Context context, String name, int version) {
		helper = new AnjiaSQLiteOpenHelper(context, name, null, version);
	}

	/**
	 * 插入
	 * 
	 * @param table
	 *            表名
	 * @param model
	 *            模型
	 */
	public void insert(String table, FileModel model) {
		SQLiteDatabase data = helper.getReadableDatabase();
		Cursor cursor = data.query(table, null, "infoid=?",
				new String[] { model.infoId + "" }, null, null, null);
		if (null == cursor || cursor.getCount() == 0) {
			Log.e("sqlite", "infoid = " + model.infoId + "insert=" + true);
			ContentValues valuesInsert = new ContentValues();
			valuesInsert.put("infoid", model.infoId);
			valuesInsert.put("uuid", model.uuid);
			valuesInsert.put("alarmtype", model.alarmType);
			valuesInsert.put("urlpath", model.urlPath);
			valuesInsert.put("filepath", model.filePath);
			valuesInsert.put("alarmtime", model.time);
			valuesInsert.put("filetype", model.fileType);
			valuesInsert.put("alarmmethod", model.alarmMethod);
			valuesInsert.put("memberid", model.memberId);
			valuesInsert.put("alarmid", model.alarmId);
			valuesInsert.put("name", model.name);
			valuesInsert.put("isnew", "F");
			valuesInsert.put("isdelete", "F");
			data.insert(table, null, valuesInsert);
		} else {
			Log.e("sqlite", "infoid = " + model.infoId + "insert=" + false);
			ContentValues valuesUpdate = new ContentValues();
			valuesUpdate.put("name", model.name);
			data.update(table, valuesUpdate, "alarmtime=? and uuid=?",
					new String[] { model.time, model.uuid });
		}
		data.close();
	}

	/**
	 * 获取数目
	 * 
	 * @param table
	 * @param columns
	 * @param selection
	 * @param selectionArgs
	 * @param groupBy
	 * @param having
	 * @param orderBy
	 * @return
	 */
	public int getCount(String table, String[] columns, String selection,
			String[] selectionArgs, String groupBy, String having,
			String orderBy) {
		int count = 0;
		SQLiteDatabase data = helper.getReadableDatabase();
		Cursor cursor = data.query(table, columns, selection, selectionArgs,
				groupBy, having, orderBy);
		if (null != cursor) {
			count = cursor.getCount();
		}
		cursor.close();
		data.close();
		return count;
	}

	/**
	 * 更新
	 * 
	 * @param table
	 * @param whereClause
	 * @param whereArgs
	 * @param key
	 * @param value
	 */
	public void update(String table, String whereClause, String[] whereArgs,
			String key, String value) {
		SQLiteDatabase data = helper.getReadableDatabase();
		ContentValues values = new ContentValues();
		values.put(key, value);
		data.update(table, values, whereClause, whereArgs);
		data.close();
	}

	/**
	 * 查询
	 * 
	 * @param table
	 * @param selection
	 * @param selectionArgs
	 * @param limit
	 * @return
	 */
	public ArrayList<FileModel> query(String table, String selection,
			String selectionArgs[], String limit) {
		SQLiteDatabase data = helper.getReadableDatabase();
		ArrayList<FileModel> list = new ArrayList<FileModel>();
		Cursor cursor = data.query(table, null, selection, selectionArgs, null,
				null, "alarmtime desc", limit);
		if (null != cursor) {
			while (cursor.moveToNext()) {
				FileModel model = new FileModel();
				model._id = cursor.getInt(cursor.getColumnIndex("_id"));
				model.uuid = cursor.getString(cursor.getColumnIndex("uuid"));
				model.alarmType = cursor.getString(cursor
						.getColumnIndex("alarmtype"));
				model.urlPath = cursor.getString(cursor
						.getColumnIndex("urlpath"));
				model.filePath = cursor.getString(cursor
						.getColumnIndex("filepath"));
				model.time = cursor.getString(cursor
						.getColumnIndex("alarmtime"));
				model.fileType = cursor.getString(cursor
						.getColumnIndex("filetype"));
				model.alarmMethod = cursor.getString(cursor
						.getColumnIndex("alarmmethod"));
				model.infoId = cursor.getInt(cursor.getColumnIndex("infoid"));
				model.alarmId = cursor.getInt(cursor.getColumnIndex("alarmid"));
				model.isnew = cursor.getString(cursor.getColumnIndex("isnew"));
				model.name = cursor.getString(cursor.getColumnIndex("name"));
				list.add(model);
			}
			cursor.close();
		}
		data.close();
		return list;
	}

	/**
	 * 删除
	 * 
	 * @param table
	 * @param whereClause
	 * @param whereArgs
	 */
	public void delete(String table, String whereClause, String[] whereArgs) {
		SQLiteDatabase data = helper.getReadableDatabase();
		data.delete(table, whereClause, whereArgs);
		data.close();
	}

	/**
	 * 清空
	 * 
	 * @param table
	 */
	public void delete(String table) {
		SQLiteDatabase data = helper.getReadableDatabase();
		data.delete(table, null, null);
		data.close();
	}

	public void newsInsert(NewsInfo model) {
		SQLiteDatabase data = helper.getReadableDatabase();
		Cursor cursor = data.query("newsinfo", null, "newsid=?",
				new String[] { model.newsId + "" }, null, null, null);
		if (null == cursor || 0 == cursor.getCount()) {
			ContentValues valuesInsert = new ContentValues();
			valuesInsert.put("newsid", model.newsId);
			valuesInsert.put("pictureurl", model.pictureUrl);
			valuesInsert.put("content", model.content);
			valuesInsert.put("title", model.title);
			valuesInsert.put("weburl", model.webUrl);
			valuesInsert.put("submittime", model.submitTime);
			valuesInsert.put("isnew", "T");
			valuesInsert.put("isdelete", "F");
			data.insert("newsinfo", null, valuesInsert);
		}
		data.close();
	}

	public ArrayList<NewsInfo> queryNews(String selection,
			String selectionArgs[], String limit) {
		SQLiteDatabase data = helper.getReadableDatabase();
		ArrayList<NewsInfo> list = new ArrayList<NewsInfo>();
		Cursor cursor = data.query("newsinfo", null, selection, selectionArgs,
				null, null, "submittime desc", limit);
		if (null != cursor) {
			while (cursor.moveToNext()) {
				NewsInfo model = new NewsInfo();
				model._id = cursor.getInt(cursor.getColumnIndex("_id"));
				model.newsId = cursor.getInt(cursor.getColumnIndex("newsid"));
				model.pictureUrl = cursor.getString(cursor
						.getColumnIndex("pictureurl"));
				model.picturePath = cursor.getString(cursor
						.getColumnIndex("picturepath"));
				model.title = cursor.getString(cursor.getColumnIndex("title"));
				model.webUrl = cursor
						.getString(cursor.getColumnIndex("weburl"));
				model.submitTime = cursor.getString(cursor
						.getColumnIndex("submittime"));
				model.content = cursor.getString(cursor
						.getColumnIndex("content"));
				model.isnew = cursor.getString(cursor.getColumnIndex("isnew"));
				list.add(model);
			}
			cursor.close();
		}
		data.close();
		return list;
	}

	public String query(String table, String column, String selection,
			String[] selectionArgs) {
		String result = null;
		SQLiteDatabase data = helper.getReadableDatabase();
		Cursor cursor = data.query(table, new String[] { column }, selection,
				selectionArgs, null, null, null);
		if (null != cursor && 0 != cursor.getCount()) {
			while (cursor.moveToNext()) {
				result = cursor.getString(cursor.getColumnIndex(column));
			}
			cursor.close();
		}
		data.close();
		return result;
	}

	public String queryColumn(String table, String column, String selection,
			String[] selectionArgs) {
		String result = null;
		SQLiteDatabase data = helper.getReadableDatabase();
		Cursor cursor = data.query(table, new String[] { column }, selection,
				selectionArgs, null, null, column + " asc");
		if (null != cursor && 0 != cursor.getCount()) {
			while (cursor.moveToNext()) {
				result = cursor.getString(cursor.getColumnIndex(column));
			}
			cursor.close();
		}
		data.close();
		return result;
	}
}
